package com.hqyj.emb.system.dao.Impl;

import com.hqyj.emb.system.dao.RoleDao;
import com.hqyj.emb.system.model.Role;
import com.hqyj.emb.system.model.User;
import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import frame.dbuitl.JdbcUtils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class RoleDaoImpl implements RoleDao {
    @Override
    public List<Role> queryAllUserRole() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_role";
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();

            List<Role> roleList = new ArrayList<>();
            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRoleId(rs.getInt("roleId"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));

                roleList.add(role_db);
            }
            return roleList;
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public int addUserRole(Role role) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO  sys_role (name,state) VALUES(?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, role.getName());
            pstmt.setString(2, role.getState());
            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deleteRoleByUserId(int roleId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "DELETE FROM sys_role WHERE roleId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, roleId);
            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Role queryUserRole(int roleId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT\n" +
            "sys_role.roleId,\n" +
                    "sys_role.`name`,\n" +
                    "sys_role.state\n" +
                    "FROM\n" +
                    "sys_userrole\n" +
                    "INNER JOIN sys_role ON sys_userrole.roleId = sys_role.roleId\n" +
                    "WHERE userId=?\n";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,roleId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRoleId(rs.getInt("roleId"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));
                return  role_db;
            }

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public int updateUserRole(Role role) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE sys_role SET name=?,state=? WHERE roleId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, role.getName());
            pstmt.setString(2, role.getState());
            pstmt.setInt(3,role.getRoleId() );
            int m = pstmt.executeUpdate();
            return m;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Role queryRoleByRoleId(int roleId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql ="SELECT * FROM sys_role WHERE roleId=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,roleId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRoleId(rs.getInt("roleId"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));
                return  role_db;
            }

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Role> queryAllUserRoleName() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_role";
            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();

            List<Role> roleList = new ArrayList<>();
            while (rs.next()) {
                Role role_db = new Role();

                role_db.setName(rs.getString("name"));


                roleList.add(role_db);
            }
            return roleList;
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public List<Role> findTj(Map<String, String[]> parameterMap) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 查询数据库
        // prepareStatement与Statement区别
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM sys_role where 1=1";
            StringBuilder sb=new StringBuilder(sql);
            Set<String> keySet = parameterMap.keySet();
            for (String key : keySet) {
                String s = parameterMap.get(key)[0];
                String value = "%"+s+"%";
                    sb.append(" and "+key+" like '"+value+"'");
            }
            pstmt = conn.prepareStatement(sb.toString());


            rs = pstmt.executeQuery();

            List<Role> roleList = new ArrayList<>();
            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRoleId(rs.getInt("roleId"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));

                roleList.add(role_db);
            }
            return roleList;
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
}
